﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {
       
       /// <summary>
       /// get all categories 
       /// </summary>
       /// <returns>list of category </returns>
        public static List<TinyItem> GetCategories()
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetCategories";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                _Adapter.SelectCommand.Parameters.Add("@x", SqlDbType.Int).Value = 0;
           
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Categories = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Categories.Add(new TinyItem());

                    Categories[i].ID = Convert.ToInt32(_DataTable.Rows[i]["CategoryID"]);
                    Categories[i].Name = Convert.ToString(_DataTable.Rows[i]["EnglishName"]);

                }
              
                return Categories;
            }
            catch (Exception)
            { return null; }
        }
        /// <summary>
        /// get list of item 
        /// </summary>
        /// <param name="LanguageCode"> languageCode from session</param>
        /// <param name="CategoryId"> CategoryId from category list</param>
        /// <returns>list of item Id,Name </returns>
       public static List<TinyItem> GetListItemsLookUps(int LanguageCode,int CategoryId)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetListItemsLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();
               _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               List<TinyItem> Items = new List<TinyItem>();

               for (int i = 0; i < _DataTable.Rows.Count; i++)
               {
                   Items.Add(new TinyItem());

                   Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                   Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

               }
               //TinyItem x = new TinyItem();
               //x.ID = 0;
               //x.Name = "";
               //Items.Insert(0, x);
               return Items;
           }
           catch (Exception)
           { return null; }

       }
/// <summary>
/// Get item 
/// </summary>
/// <param name="LanguageCode"> languageCode from session </param>
/// <param name="CategoryId">categoryId from category list</param>
/// <param name="ItemId">ItemId from ItemId</param>
/// <returns>Datatable of Item ("Name Description and so on"</returns>
       public static DataTable GetItemLookUps(int CategoryId,int ItemId)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetItemLookUp";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

           
               _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
               _Adapter.SelectCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;
       
               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               return _DataTable;
           }
           catch (Exception e)
           {
               return _DataTable;
           }
       }
        /// <summary>
        /// Get list of parent Except Me"ParentId of this Item"
        /// </summary>
        /// <param name="LanguageCode"></param>
        /// <param name="ParentItemId"></param>
        /// <returns>List of parentid</returns>
       public static List<TinyItem> GetListParentLookUps(int LanguageCode, int ParentItemId)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "ParentList";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();
               _Adapter.SelectCommand.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = ParentItemId;
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               List<TinyItem> Items = new List<TinyItem>();

               for (int i = 0; i < _DataTable.Rows.Count; i++)
               {
                   Items.Add(new TinyItem());

                   Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ParentItemId"]);
                   Items[i].Name = Convert.ToString(_DataTable.Rows[i]["ParentItemName"]);

               }
               TinyItem x = new TinyItem();
               x.ID = 0;
               x.Name = "";
               Items.Insert(0, x);
               return Items;
           }
           catch (Exception)
           { return null; }

       }
        /// <summary>
        /// Insert Item 
        /// </summary>
        /// <param name="lookups"></param>
       /// <returns>no  effect row</returns>
       public static int InsertItemLookUps(Lookups lookups)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "InsertItemLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = lookups.LanguageCode;
               _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = lookups.CategoryId;
               if (lookups.ParentItemId != 0)
               {
                   _Adapter.SelectCommand.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = lookups.ParentItemId;
               }
               else _Adapter.SelectCommand.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;
               _Adapter.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = lookups.Name;
               _Adapter.SelectCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = lookups.Description;
  
               OpenConnection();
                int roweffect = Convert.ToInt32(_Command.ExecuteNonQuery());
                CloseConnection();
                return roweffect;

           }
           catch (Exception)
           { return 0; }
       }

       public static List<TinyItem> GetItemProjectTypes(int ParentId,int LanguageCode)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetListProjectTypesLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

               _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 2;
               _Adapter.SelectCommand.Parameters.Add("@ParentId", SqlDbType.Int).Value = ParentId;
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               List<TinyItem> Items = new List<TinyItem>();

               for (int i = 0; i < _DataTable.Rows.Count; i++)
               {
                   Items.Add(new TinyItem());

                   Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                   Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

               }
              
               return Items;
           }
           catch (Exception e)
           {
               return null;
           }
       }

       public static List<TinyItem> GetItemFeilds(int ParentId, int LanguageCode)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetListProjectTypesLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

               _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 8;
               _Adapter.SelectCommand.Parameters.Add("@ParentId", SqlDbType.Int).Value = ParentId;
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               List<TinyItem> Items = new List<TinyItem>();

               for (int i = 0; i < _DataTable.Rows.Count; i++)
               {
                   Items.Add(new TinyItem());

                   Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                   Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

               }

               return Items;
           }
           catch (Exception e)
           {
               return null;
           }
       }
       public static int InsertProjectGroups(List<Lookups> lookups )
       {
           int roweffect;
           try
           {
               
               _Adapter.SelectCommand.CommandText = "InsertItemProjectGroups";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

                 _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = lookups[0].CategoryId;
              
                 
            
               if (lookups[0].ParentItemId != 0)
               {
                   _Adapter.SelectCommand.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = lookups[0].ParentItemId;
               }
               else _Adapter.SelectCommand.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;


               OpenConnection();
               int ItemId = Convert.ToInt32(_Adapter.SelectCommand.ExecuteScalar());
               CloseConnection();

               for (int i = 0; i < lookups.Count; i++)
               {
                   _Adapter.SelectCommand.CommandText = "InsertProjectGroupsDetails";
                   _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                   _Adapter.SelectCommand.Parameters.Clear();

                   _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = lookups[i].LanguageCode;
                   _Adapter.SelectCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

                   _Adapter.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = lookups[i].Name;
                   _Adapter.SelectCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = lookups[i].Description;
                   OpenConnection();
                   roweffect = Convert.ToInt32(_Adapter.SelectCommand.ExecuteNonQuery());
                   CloseConnection();
                  
               }
             
                  
               return ItemId; 

           }
           catch (Exception)
           { return 0; }
       }

    

        /// <summary>
        /// Update Item 
        /// </summary>
        /// <param name="Lookups"></param>
       /// <returns>no effect row</returns>
       public static int UpdateItemLookUps(List<Lookups> Lookups)
       {
           try
           {
         

               for (int i = 0; i < Lookups.Count ; i++)
               {

                   _Command.CommandText = "UpdateItemLookUps";
                   _Command.CommandType = CommandType.StoredProcedure;
                   _Command.Parameters.Clear();
                   _Command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = Lookups[0].CategoryId;
                   if (Lookups[0].ParentItemId != 0)
                       _Command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = Lookups[0].ParentItemId;
                   else
                       _Command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;
                   _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = Lookups[i].ItemId;
                   _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = Lookups[i].LanguageCode;
                   _Command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Lookups[i].Name;
                   _Command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = Lookups[i].Description;
                  
                   
                   OpenConnection();
                   _Command.ExecuteNonQuery();
                   CloseConnection();
                   
               
               }

               return 1;
              
           }
           catch (Exception)
           { return 0; }
       }

       public static int UpdateprojectGroups(List<Lookups> Lookups)
       {
           try
           {
               
               for (int i = 0; i < Lookups.Count; i++)
               {
                   _Adapter.SelectCommand.CommandText = "UpdateItemLookUps";
                   _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                   _Adapter.SelectCommand.Parameters.Clear();
                   _Adapter.SelectCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = Lookups[i].ItemId;
                   _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = Lookups[i].CategoryId;
                   _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = Lookups[i].LanguageCode;
                   _Adapter.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Lookups[i].Name;
                   _Adapter.SelectCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = Lookups[i].Description;
                   _Adapter.SelectCommand.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;

                   OpenConnection();
                   _Adapter.SelectCommand.ExecuteNonQuery();
                   CloseConnection();


               }

               return 1;

           }
           catch (Exception)
           { return 0; }
       }
        /// <summary>
        /// Delete Item 
        /// </summary>
        /// <param name="ItemId"></param>
        /// <param name="LanguageCode"></param>
        /// <returns>return no effect rows</returns>
       public static int DeleteItemLookUps(int ItemId, int LanguageCode)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "DeleteItemLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
               _Adapter.SelectCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;
             
               OpenConnection();
               int roweffect = Convert.ToInt32(_Command.ExecuteNonQuery());
               CloseConnection();
               return roweffect;
           }
           catch (Exception)
           { return 0; }
       }

      public static int DeleteItemprojectGroup(int ItemId)
       {
           try
           {
               _Command.CommandText = "DeleteItemProjectGroups";
               _Command.CommandType = CommandType.StoredProcedure;
               _Command.Parameters.Clear();
               _Command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;
             
               OpenConnection();
               int roweffect = Convert.ToInt32(_Command.ExecuteNonQuery());
               CloseConnection();
               return roweffect;
           }
           catch (Exception)
           { return 0; }
       }

       public static DataTable GetItemProjectGroups(int ItemId)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetItemProjectGroups";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

               _Adapter.SelectCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               return _DataTable;
           }
           catch (Exception e)
           {
               return _DataTable;
           }
       }

       public static DataTable GetProjectTypes(int ItemId,int LanguageCode)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetProjectTypeLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();

               _Adapter.SelectCommand.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

              

               return _DataTable;
           }
           catch (Exception e)
           {
               return null;
           }
       }


       public static List<TinyItem> GetListItemsEntites(int LanguageCode)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetListItemsLookUps";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();
               _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 1;
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               List<TinyItem> Items = new List<TinyItem>();

               for (int i = 0; i < _DataTable.Rows.Count; i++)
               {
                   Items.Add(new TinyItem());

                   Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                   Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

               }
               

               return Items;
           }
           catch (Exception)
           { return null; }

       }

       public static List<TinyItem> GetContactEntities(int LanguageCode)
       {
           try
           {
               _Adapter.SelectCommand.CommandText = "GetEntitiesNames_Lookups";
               _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               _Adapter.SelectCommand.Parameters.Clear();
               
               _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
               _Adapter.SelectCommand.Parameters.Add("@EntityTypeId", SqlDbType.Int).Value = 121;

               _DataTable = new DataTable();
               _Adapter.Fill(_DataTable);

               List<TinyItem> Items = new List<TinyItem>();

               for (int i = 0; i < _DataTable.Rows.Count; i++)
               {
                   Items.Add(new TinyItem());

                   Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                   Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

               }


               return Items;
           }
           catch (Exception exc)
           {
               return null;
           }
       }
    }
}



